package org.shock.ss.sms.action.system;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.shock.ss.sms.commons.db.ProxoolDbPool;

public class Exporttable {
	public void tablesinfor() throws Exception {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		HSSFWorkbook _wb = new HSSFWorkbook();
		int i = _wb.getSheetIndex("tables.xls");
		if (i >= 0)
			_wb.removeSheetAt(i);
		int result = 0;
		HSSFSheet st = _wb.createSheet("tables.xls");		
		HSSFRow row = st.createRow(result++);
		HSSFFont font = _wb.createFont();
		HSSFCellStyle cellStyle = _wb.createCellStyle();
		PreparedStatement stmt = null;
		String sql = "SELECT USER_TAB_COLS.TABLE_NAME as 表名,USER_TAB_COLS.COLUMN_NAME as 列名 , USER_TAB_COLS.DATA_TYPE as 数据类型,"+
					"USER_TAB_COLS.DATA_LENGTH as 长度,USER_TAB_COLS.NULLABLE as 是否为空,user_col_comments.comments as 备注 "+
					"FROM USER_TAB_COLS inner join user_col_comments on user_col_comments.TABLE_NAME=USER_TAB_COLS.TABLE_NAME "+
					"and user_col_comments.COLUMN_NAME=user_tab_COLS.COLUMN_NAME";
			try {
				conn = ProxoolDbPool.getConnection();
				stmt = conn.prepareStatement(sql);
				rs = stmt.executeQuery();
				ResultSetMetaData rsmd = rs.getMetaData();
				int columnCount = rsmd.getColumnCount();
				for (int j = 1; j <= columnCount; j++) {
					HSSFCell cell = row.createCell((short) (j - 1));
					font.setColor(HSSFFont.COLOR_RED);
					font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
					cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//
					cellStyle.setFont(font);
					cell.setCellStyle(cellStyle);
					cell.setCellType(HSSFCell.CELL_TYPE_STRING);
					cell.setCellValue(rsmd.getColumnName(j));
				}
				while (rs.next()) {
					row = st.createRow(result++);
					for (int j = 1; j <= columnCount; j++) {
						HSSFCell cell = row.createCell((short) (j - 1));
						cell.setCellValue(rs.getString(j));
					}
				}
			} catch (Exception e) {
				throw e;
			}finally {
			ProxoolDbPool.closeResultSet(rs);
			ProxoolDbPool.closeStatement(pstmt);
			ProxoolDbPool.closeConnection(conn);
			
		}
		
	}
	
	public static void main(String[] aaa) throws Exception{
		new Exporttable().tablesinfor();
	}
	
}
